* earliest individual webuse data is August 2012 (note that funnel data has september)
* note that customers with missing account numbers have been dropped


use Data/people_webuse.dta, clear



***********
*1. MERGING
***********

* merge with the categorical web use data from funnell search (see KissDownloads.xls)

*merge 1:1 account_number date using Data/webuse_YN.dta
*drop _merge

* merge with original webuse data 
merge 1:1 id account_number date using Data/webuse_050713.dta
drop _merge

***********
*2. COLLAPSING OVER MULTIPLE IDs
***********

** collapse over account numbers, keep alternative ids 
gen alt_id = id

order account_number

collapse (min) id (max) alt_id (firstnm) ever_viewedconserve Totalloggedin loggedin_date Totalvisits visited_date received_date opened_date NEmailOpen NEmailSent clicked_date NClicked activated_date , by(account_number date)

by account_number, sort: egen t = min(id)
replace id =t
drop t 

by account_number, sort: egen tt = max(alt_id)
replace alt_id=tt
drop tt
* there are 317 with different account numbers


***********
*3. CLEAN DATES
***********

sort account_number date
xtset account_number date

* fill in time invariant variables across dates 
foreach var in received_date opened_date clicked_date activated_date loggedin_date visited_date Totalloggedin Totalvisits {
by account_number, sort: egen t = min(`var')
replace `var'= t 
drop t
}

drop if account_number ==.

gen accessed_date = min(activated_date, loggedin_date, clicked_date, visited_date)

save Data/webuse_050913, replace







